package ac.manchester.cs.afzal.autoservd.interactions;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import ac.manchester.cs.afzal.autoservd.sql_handler.SQL_Handler;
import java.util.logging.Level;
import java.util.logging.Logger;

public class SQL_Handler_Interactions extends SQL_Handler
{

    private Vector distinct_sen_ids, distinct_sen_ids_2, occurrence_values,
            occurrence_values_2, descriptor_ids;

    /**
     * Retrieves all entries from table Semantic_Classes in BMC_2009_2 Table
     * @return
     */
    public Vector get_all_semantic_class_instances()
    {
        Vector semantic_class_instances = new Vector();
        try
        {
            String query = "Select distinct Value from semantic_classes where " +
                    "Value NOT LIKE '&%' order by Value";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                semantic_class_instances.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding semantic_class_instances: " + sqlex.getStackTrace());
        }
        return semantic_class_instances;
    }

    /**
     * Retrieves all values from ontology table (Mygrid Ontology in this case)
     * @param ontology_name
     * @return
     */
    public Vector get_all_ontology_values(String ontology_name)
    {
        Vector semantic_instances = new Vector();
        try
        {
            String query = "SELECT distinct Value from " + ontology_name;
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                semantic_instances.add(rs.getString("Value"));
            }
        }
        catch (SQLException ex)
        {
            Logger.getLogger(SQL_Handler_Interactions.class.getName()).log(Level.SEVERE, null, ex);
        }
        return semantic_instances;
    }

    /**
     * Returns the occurrence values and the distinct sentence ids where they
     * appear. 
     * @param occurrence_values_ = comma separated occurrence values
     * @return
     */
    public Vector get_occurrences_distinct_sen_ids(String occurrence_values_)
    {
        occurrence_values = new Vector();
        distinct_sen_ids = new Vector();

        try
        {
            String query = "SELECT distinct o.Value,sm.D_Sen_ID FROM Occurrences o," +
                    "Sentence_Mapping sm where o.Value IN (" + occurrence_values_ + ") " +
                    "and o.Sen_ID =  sm.Sen_ID";
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                occurrence_values.add(rs.getString("Value"));
                distinct_sen_ids.add(rs.getString("D_Sen_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Finding document name and sentence id for each semantic type : " +
                    sqlex.getStackTrace());
        }
        return occurrence_values;
    }

    /**
     * Returns the occurrence values which appear in a given distinct sentence id
     * @param id
     * @return
     */
    public Vector get_occurrences_per_distinct_sentence_id(int d_sen_id)
    {
        Vector occurrences = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT o.Value FROM Sentence_Mapping sm, Occurrences o " +
                    "where sm.D_Sen_ID = " + d_sen_id + " and sm.Sen_ID = o.Sen_ID";

            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                occurrences.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Getting Cooccuring terms " + sqlex.getMessage());
        }
        return occurrences;
    }

    /**
     * Returns the occurrence values and their respective distinct sentence ids where the
     * bioinformatics service descriptors appear
     * @param semantic_instances
     * @return
     */
    public Vector get_occurrences_distinct_sen_ids_for_descriptors(Vector<String> semantic_instances)
    {
        occurrence_values_2 = new Vector();
        distinct_sen_ids_2 = new Vector();
        String query = "";

        try
        {
            for (String semantic_instance : semantic_instances)
            {
                String store_semantic_instance = semantic_instance;

                semantic_instance = semantic_instance.toLowerCase();
                //semantic_instance = semantic_instance.replaceAll(" ", "%");
                //temp1 = temp1.replaceAll(" ","%' or o.Value LIKE '%");

                query = "SELECT distinct o.Value,sm.D_Sen_ID FROM Occurrences o,Sentence_Mapping " +
                        "sm, Sentences s where o.Value LIKE '%" + semantic_instance + "%' and " +
                        "o.Sen_ID = sm.Sen_ID";
                statement = connection.createStatement();

                ResultSet rs = statement.executeQuery(query);
                while (rs.next())
                {
                    occurrence_values_2.add(store_semantic_instance);
                    distinct_sen_ids_2.add(rs.getString("D_Sen_ID"));
                }
                statement.close();
            }
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: D_Sen_ID and Occurrence : " + sqlex.getMessage());
        }
        return occurrence_values_2;
    }

    /**
     * Returns the occurrence values and their respective distinct sentence ids where the
     * terms appearing in ontology appear.
     * @param semantic_instances
     * @return
     */
    public Vector get_occurrences_distinct_sen_ids_for_ontology(Vector<String> semantic_instances)
    {
        occurrence_values_2 = new Vector();
        distinct_sen_ids_2 = new Vector();
        String query = "";

        try
        {
            for (String semantic_instance : semantic_instances)
            {
                String store_semantic_instance = semantic_instance;

                semantic_instance = semantic_instance.toLowerCase();
                semantic_instance = semantic_instance.replaceAll(" ", "%");
                //temp1 = temp1.replaceAll(" ","%' or o.Value LIKE '%");

                query = "SELECT distinct o.Value,sm.D_Sen_ID FROM Occurrences o,Sentence_Mapping " +
                        "sm, Sentences s where o.Value LIKE '%" + semantic_instance + "%' and " +
                        "o.Sen_ID = sm.Sen_ID";
                statement = connection.createStatement();

                ResultSet rs = statement.executeQuery(query);
                while (rs.next())
                {
                    occurrence_values_2.add(store_semantic_instance);
                    distinct_sen_ids_2.add(rs.getString("D_Sen_ID"));
                }
                statement.close();
            }
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: D_Sen_ID and Occurrence : " + sqlex.getMessage());
        }
        return occurrence_values_2;
    }

    public Vector get_all_descriptor_values()
    {
        Vector descriptors = new Vector();
        String query = "SELECT Filtered_Descriptor FROM ___bioinformatics_service_descriptors " +
                "where Filtered_Descriptor NOT LIKE '%EMPTY%' and char_length(Filtered_Descriptor) > 4";

        try
        {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                descriptors.add(rs.getString("Filtered_Descriptor"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Descriptor Values from Dictionary : " + sqlex.getMessage());
        }
        return descriptors;
    }

    public Vector get_descriptor_values()
    {
        Vector values = new Vector();
        descriptor_ids = new Vector();

        String query = "SELECT Descriptor,Descriptor_ID FROM ___bioinformatics_service_descriptors p " +
                "where Value NOT LIKE '%EMPTY%' and char_length(Value) > 4";

        try
        {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                values.add(rs.getString("Descriptor"));
                descriptor_ids.add(rs.getString("Descriptor_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: ID,Value from Dictionary : " + sqlex.getMessage());
        }
        return values;
    }

    public Vector get_matching_argument_value(String value_to_match)
    {
        Vector arguments = new Vector();
        occurrence_values = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT ID,Term FROM __predicates_arguments_hybrid where " +
                    "Arg1_X LIKE '%" + value_to_match + " %' OR Arg1_X LIKE '% " + value_to_match + "%' OR Arg2_X LIKE '%" + value_to_match +
                    " %' OR Arg2_X LIKE '% " + value_to_match + "%'";

            ResultSet rs = statement.executeQuery(query);
            while (rs.next())
            {
                arguments.add(rs.getInt("ID"));
                occurrence_values.add(rs.getString("Term"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Argument ID and term : " + sqlex.getMessage());
        }
        return arguments;
    }

    public Vector get_distinct_sen_ids()
    {
        return distinct_sen_ids;
    }

    public Vector get_distinct_sen_ids_2()
    {
        return distinct_sen_ids_2;
    }

    public Vector get_occurrence_values()
    {
        return occurrence_values;
    }

    public Vector get_occurrence_values_2()
    {
        return occurrence_values_2;
    }

    public Vector get_descriptor_ids()
    {
        return descriptor_ids;

    }
}

/*                    query = "SELECT distinct d.Value as docs,n.Value as terms,s.SenID,s.ID FROM nouns n, noun_profiles np, "+
"sentences s,documents d where n.Type = 1 and CAST(n.Value AS BINARY) REGEXP '(.)*[[:upper:]]+(.)*' "+
"and n.ID = np.Noun_ID and np.Occurence_ID = s.ID and s.DocID = d.ID";
 */ // For proper Nouns

        